package excelIO;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class ReadXLS {

    public static void main(String[] args) {

        String fileName = "c:\\800_fix\\400_JavaDev\\tmp\\testPOIRead.xls";
        //Read an Excel File and Store in a ArrayList
        //Notice:ArrayList is not thread-safe. You would have to use
        //Collections.synchronizedList(<your ArrayList>) instead for synch access.
        ArrayList dataHolder = readExcelFile(fileName);
        //Print the data read
        printCellDataToConsole(dataHolder);
    }

    public static ArrayList readExcelFile(String fileName) {
        /** --Define a ArrayList
        --Holds ArrayList Of Cells
         */
        ArrayList cellVectorHolder = new ArrayList();

        try {
            /** Creating Input Stream**/
            FileInputStream myInput = new FileInputStream(fileName);

            Workbook myWorkBook = WorkbookFactory.create(myInput);

            /** Get the first sheet from workbook**/
            Sheet mySheet = myWorkBook.getSheetAt(0);

            /** We now need something to iterate through the cells.**/
            Iterator rowIter = mySheet.rowIterator();

            while (rowIter.hasNext()) {
                Row myRow = (Row) rowIter.next();
                Iterator cellIter = myRow.cellIterator();
                ArrayList cellStoreVector = new ArrayList();
                while (cellIter.hasNext()) {
                    Cell myCell = (Cell) cellIter.next();
                    cellStoreVector.add(myCell);
                }
                cellVectorHolder.add(cellStoreVector);
            }
        } catch (IOException e) {
        } catch (InvalidFormatException e) {
        }
        return cellVectorHolder;
    }

    private static void printCellDataToConsole(ArrayList dataHolder) {

        for (int i = 0; i < dataHolder.size(); i++) {
            ArrayList cellStoreVector = (ArrayList) dataHolder.get(i);

            for (int j = 0; j < cellStoreVector.size(); j++) {
                Cell myCell = (Cell) cellStoreVector.get(j);
                String stringCellValue = myCell.toString();
                System.out.print(stringCellValue + "\t");
            }
            System.out.println();
        }
    }
}
